package com.freework.report.xmlreport.groupBean.dao;

import java.util.ArrayList;
import java.util.List;

import com.freework.Cfg;
import com.freework.freedbm.cfg.fieldcfg.type.SQLType;
import com.freework.report.base.service.filterCondition.Condition;
import com.freework.report.xmlreport.groupBean.vo.Col;
import com.freework.report.xmlreport.groupBean.vo.Cols;
import com.freework.report.xmlreport.groupBean.vo.Table;
import com.freework.util.SqlUtil;
import com.freework.util.unmodifiableMap.KeysIndex;

public class CreateSQLBuiderBean {



	public SQLBuilderBean createBean(Table table, Cols allCols){
		SQLBuilderBean bean=new SQLBuilderBean();		
		bean.setByname(table.getByname());
		bean.setTablename(table.getName());
		List<Condition> condition=table.getCondition();
		bean.setConditions(condition!=null&&condition.size()>0?condition.toArray(new Condition[condition.size()]):null);
		bean.setAddwhere(table.getWhere()!=null&&!table.getWhere().equals(""));
		setCols(bean,table,allCols);
		bean.setSql(bilidSQL(table.getByname(),table.getName(),table.getWhere(),bean.getCols()));
		return bean;
		
	}
	
	boolean contains(ArrayList<DBCol> list,String name){
		if (name == null) {
		    return false;
		} else {
		    for (int i = 0; i < list.size(); i++)
			if (name.equals(list.get(i).getName()))
			    return true;
		}
		return false;
	}
	
	
	
	public SQLType getSQLType(String type){
		if(type==null)
			return  Cfg.String;
		if(type.indexOf("sum")!=-1||type.indexOf("amt")!=-1){
			return Cfg.BigDecimal;
		}else if(type.indexOf("count")!=-1){
			return Cfg.Integer;
		}else if(type.indexOf("dic")!=-1){
			return Cfg.String;
		}else if(type.indexOf("max")!=-1){
			return Cfg.BigDecimal;
		}else if(type.indexOf("min")!=-1){
			return Cfg.BigDecimal;
		}else
			return Cfg.BigDecimal;
		
	}
	
	public void setCols(SQLBuilderBean bean,Table table, Cols allCols) {
		ArrayList<DBCol> list=new ArrayList<DBCol>();
		ArrayList<String> nameSet=new ArrayList<String>();
		List<Col> cols=allCols.getCol();
		List<Col> groupCols=allCols.getGroup().getCol();
		for (Col col : groupCols) {
			if(col.getTable()==null||"".equals(col.getTable())||table.getName().equals(col.getTable())){
				
				DBCol	dbcol =new DBCol();
				dbcol.setName(col.getSourceName());
				col.setSourceName(col.getSourceName().replace(",", "_"));
				dbcol.setType(Cfg.String);
				dbcol.setAggregate(null);
				dbcol.setIsgroup(true);
				list.add(dbcol);
				nameSet.add(col.getSourceName());

			}
		}
		for (int i = 0; i < cols.size(); i++) {
			Col col=cols.get(i);

			if(col.getTable()==null||"".equals(col.getTable())||table.getName().equals(col.getTable())){

				String name=col.getSourceName();
				String tagertName=col.getTagertName();
				List<String> nameParams= SqlUtil.getSqlParameter2(tagertName, '{', '}');
				for (String nameParam : nameParams) {
					if(!contains(list,nameParam)){
					DBCol	dbcol =new DBCol();
					dbcol.setName(nameParam);
					dbcol.setType(Cfg.String);
					dbcol.setAggregate(null);
					dbcol.setIsgroup(true);
					list.add(dbcol);
					nameSet.add(nameParam.replace(",", "_"));
					}
				}
				if(!contains(list,name)){
					DBCol	dbcol =new DBCol();
					dbcol.setName(name);
					dbcol.setType(getSQLType(col.getType()));
					dbcol.setAggregate(col.getType());
					col.setSourceName(col.getSourceName().replace(",", "_"));
					nameSet.add(col.getSourceName());
					list.add(dbcol);
				}else{
					col.setSourceName(col.getSourceName().replace(",", "_"));
				}
			}
		}
		bean.setKeysIndex(new KeysIndex(nameSet.toArray(new String[nameSet.size()])));
		bean.setCols(list.toArray(new DBCol[list.size()]));
		
	}
	
	public String bilidSQL(String byname1,String tableName,String where,DBCol[] cols){
		String byname=byname1==null?tableName:byname1;
		boolean groupPand=false;		
		StringBuilder sql=new StringBuilder(36+cols.length*50+tableName.length()+where.length());
		sql.append("select ");
		StringBuilder group=new StringBuilder();
		for (int i = 0; i < cols.length; i++) {
			if(i!=0)
				sql.append(",");
			if(!cols[i].isIsgroup()){
				if(cols[i].getAggregate()==null)
					sql.append(getColName(byname,cols[i].getName())).append(" ");
				else
					sql.append(cols[i].getAggregate()).append("(").append(byname).append(".").append(cols[i].getName()).append(")");
			}else{
				sql.append(getColName(byname,cols[i].getName()));
				if(groupPand)
					group.append(",");
				else
					groupPand=true;
				group.append(getColName(byname,cols[i].getName()));
			}
		}
		sql.append(" from ").append(tableName);
		if(byname1!=null)
			sql.append(" ").append(byname1);
		if(!where.equals("")){
			sql.append(" where ").append(where);
		}
			sql.append(" ${thisWhereReplace} ");

		if(groupPand)
			sql.append(" group by ").append(group);		
		return sql.toString();
	}
	
	public String getColName(String byname,String name){
		if(name.indexOf("(")!=-1&&name.indexOf(")")!=-1)
			return name;
		else
			return byname+"."+name;
		
	}
}
